<?php
namespace Libraries;
class CDb {
//================================ BANK TRANS ====================================
    public static function save_bank_trans($type_id, $trans_no, $ref,
            $trans_date, $amount, $id_bank, $memo_, $id_user, $id_kavling = null) {
        return "INSERT INTO psn_bank_trans(type_id, trans_no, ref, trans_date, 
            amount, id_bank, memo_, id_user, id_kavling)
		VALUES ($type_id, $trans_no, '$ref', '$trans_date', $amount,  
                $id_bank, '$memo_', $id_user, $id_kavling);";
    }
//================================ PROYEK ===============================    
    public static function insert_proyek($nama, $lokasi, $tgl_mulai,
            $tgl_selesai, $status) {
        return "INSERT INTO psn_proyek
	(nama, lokasi, tgl_mulai, tgl_selesai, status) VALUES 
        ('$nama', '$lokasi', '$tgl_mulai', '$tgl_selesai', $status);";
    }
    public static function update_proyek($id, $nama, $lokasi, $tgl_mulai,
            $tgl_selesai, $status) {
        if (trim($tgl_mulai) == "") $tgl_mulai = 'NULL';
        else $tgl_mulai = "'$tgl_mulai'";
        if (trim($tgl_selesai) == "") $tgl_selesai = 'NULL';
        else $tgl_selesai = "'$tgl_selesai'";
        return "UPDATE psn_proyek SET nama = '$nama', lokasi = '$lokasi',
	           status = $status,tgl_mulai = $tgl_mulai,tgl_selesai = $tgl_selesai
                       WHERE id_proyek = $id;";
    }
    public static function get_proyek_by_id($id, $column = '*') {
        if (is_array($column)) {
            $column = implode(', ', $column);
        }
        return "SELECT $column FROM psn_proyek where id_proyek = $id;";
    }
//================================ TRANSAKSI BAHAN ===============================
    public static function get_transaksi_bahan() {
        $voided = CDb::get_voided(TRANSAKSIBAHAN);
        return "SELECT * FROM psn_transaksi_bahan where id_transaksi_bahan NOT IN($voided);";
    }
    public static function get_transaksi_bahan_by_id($id, $column = '*') {
        if (is_array($column)) {
            $column = implode(', ', $column);
        }
        return "SELECT $column FROM psn_transaksi_bahan where id_transaksi_bahan = $id;";
    }
    public static function view_transaksi_bahan($id) {
        return "SELECT ptb.nama_barang, ptb.jml, ptb.harga_satuan, ptb.total, ptb.ref,
            ptb.tgl_trans, pk.nama, pb.nama_akun
        FROM hineni.psn_transaksi_bahan ptb 
	INNER JOIN hineni.psn_kavling pk ON (ptb.id_kavling = pk.id_kavling)  
	INNER JOIN hineni.psn_bank pb ON (ptb.id_bank = pb.id_bank)
        WHERE ptb.id_transaksi_bahan = $id";
    }
//================================ KAS MASUK ====================================
    public static function insert_kas_masuk($doc_ref, $no_bukti, $amount,
            $entry_time, $trans_date, $trans_via, $note, $id_bank, $id_kavling) {
        return "INSERT INTO psn_kas_masuk
                (doc_ref, no_bukti, amount, entry_time, trans_date, trans_via, note, 
                id_bank, id_kavling) VALUES 
                ('$doc_ref', '$no_bukti', $amount, now(), '$trans_date', '$trans_via', 
                    '$note', $id_bank, $id_kavling );";
    }
    public static function view_kas_masuk($id) {
        return "SELECT pkm.doc_ref, pkm.no_bukti, pkm.amount, pkm.entry_time, 
            pkm.trans_date, pkm.trans_via, pkm.note, pb.nama_akun as bank, 
            pk.nama as kavling FROM psn_kas_masuk pkm 
	INNER JOIN psn_bank pb ON (pkm.id_bank = pb.id_bank)  
	INNER JOIN psn_kavling pk ON (pkm.id_kavling = pk.id_kavling)
        WHERE pkm.kas_masuk_id = $id";
    }
    public static function get_kas_masuk() {
        $voided = CDb::get_voided(KAS_MASUK);
        return "SELECT * FROM psn_kas_masuk where kas_masuk_id NOT IN($voided);";
    }
    public static function get_kas_masuk_by_id($id, $column = '*') {
        if (is_array($column)) {
            $column = implode(', ', $column);
        }
        return "SELECT $column FROM psn_kas_masuk where kas_masuk_id = $id;";
    }
//================================ VOIDED ====================================    
    public static function insert_voided($type, $id, $date, $memo) {
        return "INSERT INTO psn_voided
	(`type`, id, date_, memo_) VALUES ( $type, $id, '$date', '$memo' );";
    }
    public static function get_voided($type) {
        return "SELECT vo.id
                FROM psn_voided vo WHERE vo.type = $type";
    }
//================================ USER ====================================    
    public static function get_user_by_id($id, $column = '*') {
        if (is_array($column)) {
            $column = implode(', ', $column);
        }
        return "SELECT $column FROM psn_users where id_user = $id;";
    }
//================================ KAVLING ====================================    
    public static function get_kavling_by_id($id, $column = '*') {
        if (is_array($column)) {
            $column = implode(', ', $column);
        }
        return "SELECT $column FROM psn_kavling where id_kavling = $id;";
    }
    public static function get_kavling_by_proyek_id($id, $column = '*') {
        if (is_array($column)) {
            $column = implode(', ', $column);
        }
        return "SELECT $column FROM psn_kavling where id_proyek = $id;";
    }
//================================ LAPORAN ====================================    
    public static function get_lap_trans_bahan($from_date, $to_date, $id_kavling) {
        $voided = CDb::get_voided(TRANSAKSIBAHAN);
        return "SELECT SUM(tb.total) FROM psn_transaksi_bahan tb
                WHERE (tb.tgl_trans BETWEEN '$from_date'
                    AND '$to_date') AND tb.id_kavling = $id_kavling
                AND tb.id_transaksi_bahan NOT IN ($voided)";
    }
}
?>